﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using OfficeOpenXml;

namespace MasterListCreator.Classes
{
    public class EPPlusHelper
    {
        private static int maxIndent; 

        public static void CreateExcelFile(string fileName, string outputPath, string sheetName, System.Data.DataTable data)        
        {
            try
            {
                if (System.IO.File.Exists(fileName))
                {
                    System.IO.File.Delete(fileName);
                }
            }
            catch (Exception)
            {                
                throw;
            }           

            System.IO.FileInfo file = new System.IO.FileInfo(fileName);
            using (ExcelPackage pck = new ExcelPackage(file))
            {
                // Create the worksheet                
                ExcelWorksheet ws = pck.Workbook.Worksheets.Add(sheetName);

                // Load the datatable into the sheet, starting from dataRange.   
                //string dataRange = string.Format("{0}{1}", ExcelHelper.ColumnIndexToName(PublicParam.START_COL), PublicParam.START_ROW);
                //ws.Cells[dataRange].LoadFromDataTable(data, true, OfficeOpenXml.Table.TableStyles.Medium23);               

                string addName = string.Empty;
                string addLastModified = string.Empty;
                string addSize = string.Empty;
                string path = string.Empty;
                int indent = 0;
                maxIndent = GetMaxIndent(data);
                for (int i = 0; i < data.Rows.Count; i++)
                {
                    indent = int.Parse(data.Rows[i][PublicName.ENT_RECORDENTRY_INDENT].ToString());
                    addName = string.Format("{0}{1}", ExcelHelper.ColumnIndexToName(PublicParam.START_COL + indent), PublicParam.START_ROW + i);
                    path = data.Rows[i][PublicName.ENT_RECORDENTRY_FULLPATH].ToString();
                    FillValue(ref ws, addName, Utility.GetName(path), path, GetUriKind(PublicParam.URI_KIND));
                    MergeCell(ref ws, ExcelHelper.GetAddress(PublicParam.START_COL + indent, 
                                                            PublicParam.START_ROW + i, 
                                                            PublicParam.START_COL + maxIndent, 
                                                            PublicParam.START_ROW + i));

                    addLastModified = string.Format("{0}{1}", ExcelHelper.ColumnIndexToName(PublicParam.START_COL + maxIndent + 1), PublicParam.START_ROW + i);
                    FillValue(ref ws, addLastModified, data.Rows[i][PublicName.ENT_RECORDENTRY_LAST_MODIFIED].ToString());

                    addSize = string.Format("{0}{1}", ExcelHelper.ColumnIndexToName(PublicParam.START_COL + maxIndent + 2), PublicParam.START_ROW + i);
                    FillValue(ref ws, addSize, data.Rows[i][PublicName.ENT_RECORDENTRY_SIZE].ToString());
                }

                InsertHeader(ref ws, PublicParam.START_COL, PublicParam.START_ROW, new string[] { "Name", "Last Modified", "Size" } );
                pck.Save();
            }
        }

        private static int GetMaxIndent(System.Data.DataTable table)
        {
            List<int> indents = new List<int>();
            for (int i = 0; i < table.Rows.Count; i++)
            {
                indents.Add(int.Parse(table.Rows[i][PublicName.ENT_RECORDENTRY_INDENT].ToString()));
            }

            return indents.Max();
        }

        private static void FillValue(ref ExcelWorksheet sheet, string address, string value, string url, UriKind uriKind)
        {
            //var namedStyle = sheet.Workbook.Styles.CreateNamedStyle("HyperLink");   //This one is language dependent
            var cell = sheet.Cells[address];
            cell.Value = value;
            
            if (Utility.IsFolder(url))
            {
                if (PublicParam.ReadConfig(PublicName.HYPERLINK_FOLDER) == "1")
                {
                    cell.Hyperlink = new Uri(url, uriKind);
                    cell.Style.Font.UnderLine = true;
                    cell.Style.Font.Color.SetColor(System.Drawing.Color.Blue);
                }
            }
            else
            {
                if (PublicParam.ReadConfig(PublicName.HYPERLINK_FILE) == "1")
                {
                    cell.Hyperlink = new Uri(url, uriKind);
                    cell.Style.Font.UnderLine = true;
                    cell.Style.Font.Color.SetColor(System.Drawing.Color.Blue);
                }
            }   
        }

        private static void InsertHeader(ref ExcelWorksheet sheet, int startCol, int startRow, string[] data)
        {
            InsertRow(ref sheet, startRow, 1);

            string address = string.Empty;

            address = string.Format("{0}{1}", ExcelHelper.ColumnIndexToName(startCol), startRow);
            FillValue(ref sheet, address, data[0]);
            address = ExcelHelper.GetAddress(startCol, startRow, startCol + maxIndent , startRow);
            MergeCell(ref sheet, address);
            for (int i = 1; i < data.Length; i++)
            {
                address = string.Format("{0}{1}", ExcelHelper.ColumnIndexToName(startCol + i + maxIndent), startRow);
                FillValue(ref sheet, address, data[i]);
            }

            address = ExcelHelper.GetAddress(startCol, startRow, startCol + data.Length - 1 + maxIndent, startRow);
            sheet.Cells[address].Style.Font.Bold = true;
            sheet.Cells[address].Style.Fill.PatternType = OfficeOpenXml.Style.ExcelFillStyle.Solid;
            sheet.Cells[address].Style.Fill.BackgroundColor.SetColor(System.Drawing.Color.AntiqueWhite);

            // Fix Header Width -- not affect
            //for (int i = startCol; i < startCol + data.Length + 1; i++)
            //{
            //    sheet.Column(i).AutoFit(0);                    
            //}
        }

        private static void FillValue(ref ExcelWorksheet sheet, string address, string value)
        {
            var cell = sheet.Cells[address];
            cell.Value = value;
        }

        private static void InsertRow(ref ExcelWorksheet sheet, int index, int number)
        {
            sheet.InsertRow(index, number);
        }

        private static void MergeCell(ref ExcelWorksheet ws, string address)
        {
            ws.Cells[address].Merge = true;
        }

        private static UriKind GetUriKind(int index)
        {
            switch (index)
            {
                case 0:
                    return UriKind.Absolute;                    
                case 1:
                    return UriKind.Relative;   
                case 2:
                    return UriKind.RelativeOrAbsolute;
                default:
                    return UriKind.Absolute;
            }
        }

        private static UriKind GetUriKind(string uriKindName)
        {
            switch (uriKindName.ToLower())
            {
                case "absolute":
                    return GetUriKind(0);
                case "relative":
                    return GetUriKind(1);
                case "relativeorabsolute":
                    return GetUriKind(2);
                default:
                    return GetUriKind(3);
            }
        }
    }
}
